package com.example.submit_system.controller;

import cn.hutool.core.date.DateUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.example.submit_system.mapper.DataBaseTableMapper;
import com.example.submit_system.model.TestLpyEmployee;
import com.example.submit_system.model.TestLpyEmployeeSubmit;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.InputStream;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@RestController
@RequestMapping("/database")
@CrossOrigin(origins = "*", allowedHeaders = "*", methods = {RequestMethod.GET, RequestMethod.POST, RequestMethod.PUT, RequestMethod.DELETE, RequestMethod.OPTIONS})
public class DataBaseTableController {

    @Autowired
    private DataBaseTableMapper dataBaseTableMapper;

    /* Excel文件中的数据导入到数据库 */
    @RequestMapping("/import")    //@RequestParam("file") 接收客户端上传的文件
    public void imp(@RequestParam("file") MultipartFile file) throws Exception {
        //先清空表再插入表
        //dataBaseTableMapper.deleteAll();
        InputStream inputStream = file.getInputStream();
        //创建Excel读取器,Excel ---->  数据库表
        /*
            Hutool 的ExcelUtil.getReader()创建一个 ExcelReader 对象，用于后续解析 Excel 数据
            // 1. 通过文件路径创建
            ExcelReader reader = ExcelUtil.getReader("path/to/file.xlsx");
            // 2. 通过输入流创建(适用于网络流或内存流)
            ExcelReader reader = ExcelUtil.getReader(inputStream);
            // 3. 指定工作表(Sheet)页,file可以是文件路径，也可以是File实例化对象
            File file = new File("D:/data/employees.xlsx");
            ExcelReader reader = ExcelUtil.getReader(inputStream, 1); // 从第2行开始读取
        */
        ExcelReader reader = ExcelUtil.getReader(inputStream);
        //将Excel表头(如"员工编号")映射到Java对象的属性名(如employeeId)
        /*默认将首行作为表头，自动映射到 Java 对象属性(需属性名与列名一致)
        支持自定义表头别名(通过 addHeaderAlias() 方法）*/
        reader.addHeaderAlias("员工编号", "employeeId");
        reader.addHeaderAlias("姓名", "name");
        reader.addHeaderAlias("性别", "gender");
        reader.addHeaderAlias("年龄", "age");
        reader.addHeaderAlias("部门", "department");
        reader.addHeaderAlias("职位", "position");
        reader.addHeaderAlias("入职日期", "entryDate");
//        List<List<Object>> headerRows = reader.read(0, 1);
//        System.out.println("Excel表头：" + headerRows);
        //将Excel中所有数据行转换为 TestLpyEmployee 对象的列表
        /*
            readAll() 读取全部数据,自动映射到对象
            read(int start, int end) 分页读取
            readRow(int index) 读取单行数据
        * */
        List<TestLpyEmployee> testLpyEmployees = reader.readAll(TestLpyEmployee.class);
        //批量插入数据
        dataBaseTableMapper.batchInsert(testLpyEmployees);
    }

    /* 数据库test_lpy_employee_submit表数据加载到Excel文件 */
    @RequestMapping("/export")
    public void export(HttpServletResponse response) throws Exception {
        ArrayList<TestLpyEmployeeSubmit> testLpyEmployeeSubmits = dataBaseTableMapper.selectAll();
        for(TestLpyEmployeeSubmit emp : testLpyEmployeeSubmits)
        {
            System.out.printf("employee_id:"+emp.getEmployeeId()  + " name:" + emp.getName()
                    + " gender:" + emp.getGender() + " 年龄:" + emp.getAge() + " 部门:" + emp.getDepartment()
                    + " 职位:" + emp.getPosition() + " 入职日期:" + emp.getEntryDate());
            System.out.println();
        }
        // 构造一个List<Map<String, Object>>，将日期格式化为字符串,因为Excel不能识别日期格式
        List<Map<String, Object>> exportList = new ArrayList<>();
        for (TestLpyEmployeeSubmit emp : testLpyEmployeeSubmits) {
            Map<String, Object> map = new LinkedHashMap<>();
            map.put("employeeId", emp.getEmployeeId());
            map.put("name", emp.getName());
            map.put("gender", emp.getGender());
            map.put("age", emp.getAge());
            map.put("department", emp.getDepartment());
            map.put("position", emp.getPosition());
            //将日期格式化为字符串 Date ---> String
            map.put("entryDate", emp.getEntryDate() == null ? "" : DateUtil.format(emp.getEntryDate(), "yyyy-MM-dd"));
            map.put("fileDate",  emp.getFileDate() == null ? "" : DateUtil.format(emp.getFileDate(), "yyyy-MM-dd"));
            exportList.add(map);
        }

        //创建一个Excel写入器，数据库数据 ---> Excel文件
        //参数 true 表示生成 .xlsx 格式文件
        ExcelWriter writer = ExcelUtil.getWriter(true);

        //设置列宽,列索引从0开始
        //代码中单独设置第7列(索引6)和第8列(索引7)的宽度为20像素,通常是为了适配特定列的内容长度
        //自适应列宽，可使用 autoSizeColumnAll() 方法
        writer.setColumnWidth(6, 20);
        writer.setColumnWidth(7, 20);
        //将 Java 对象的字段名映射为 Excel 表头显示名称
        writer.addHeaderAlias("employeeId", "员工编号");
        writer.addHeaderAlias("name", "姓名");
        writer.addHeaderAlias("gender", "性别");
        writer.addHeaderAlias("age", "年龄");
        writer.addHeaderAlias("department", "部门");
        writer.addHeaderAlias("position", "职位");
        writer.addHeaderAlias("entryDate", "入职日期");
        writer.addHeaderAlias("fileDate", "报送日期");
        //exportList：待导出的数据列表 Map集合
        //true：表示写入表头,表头就是字段名(若已通过 addHeaderAlias 定义别名，则表头为别名值)
        writer.write(exportList, true);

        //设置响应头  模板照抄即可修改表名
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String fileName = URLEncoder.encode("员工信息报送表", "UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        ServletOutputStream outputStream = response.getOutputStream();
        writer.flush(outputStream, true);
        outputStream.close();
        writer.close();
    }

    //数据转换,填报表加上fileDate字段向报送表转换
    @RequestMapping("/convert")
    @ResponseBody
    public String convert() {
        String today = DateUtil.format(DateUtil.date(), "yyyyMMdd");
        dataBaseTableMapper.producerSingle(today);
        return "数据转换成功，日期:" + today;
    }

    //下载Excel模版
    @RequestMapping("/downloadTemplate")
    public void downloadTemplate(HttpServletResponse response) throws Exception {
        // 创建Excel模板
        ExcelWriter writer = ExcelUtil.getWriter(true);
        writer.addHeaderAlias("employeeId", "员工编号");
        writer.addHeaderAlias("name", "姓名");
        writer.addHeaderAlias("gender", "性别");
        writer.addHeaderAlias("age", "年龄");
        writer.addHeaderAlias("department", "部门");
        writer.addHeaderAlias("position", "职位");
        writer.addHeaderAlias("entryDate", "入职日期");

        //仅写入 Excel 表头,不包含任何数据行
        //参数new ArrayList<>():表示要写入的数据列表为空，即没有实际数据行
        //参数true:表示仅写入表头,表头就是字段名(Header)，不包含数据内容
        writer.write(new ArrayList<>(), true);
        // 写入一行示例数据
        List<Map<String, Object>> example = new ArrayList<>();
        Map<String, Object> row = new LinkedHashMap<>();
        row.put("employeeId", "1001");
        row.put("name", "张三");
        row.put("gender", "男");
        row.put("age", "28");
        row.put("department", "技术部");
        row.put("position", "工程师");
        row.put("entryDate", "2023-01-01");
        example.add(row);
        writer.write(example, true);

        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        String fileName = URLEncoder.encode("员工信息报送表模板", "UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        ServletOutputStream outputStream = response.getOutputStream();
        writer.flush(outputStream, true);
        outputStream.close();
        writer.close();
    }

    /*返回数据库test_lpy_employee表的所有员工数据*/
    //该方法用于分页查询员工列表,并返回包含员工数据及总数的响应
    @RequestMapping("/viewEmployees")
    @ResponseBody
    public Map<String, Object> viewEmployees(@RequestParam(defaultValue = "1") int page, @RequestParam(defaultValue = "8") int size)
    {
        //跳过多少条数据
        int offset = (page - 1) * size;

        //分页数据获取:调用 selectAllEmployeesPaginated 方法,传入 offset 和 size,返回当前页的员工列表
        List<TestLpyEmployee> employees = dataBaseTableMapper.selectAllEmployeesPaginated(offset, size);
        //总数查询:调用 getTotalEmployeeCount 方法，获取员工总数，用于计算总页数
        int totalCount = dataBaseTableMapper.getTotalEmployeeCount();

        Map<String, Object> response = new LinkedHashMap<>();
        response.put("employees", employees);
        response.put("totalCount", totalCount);
        return response;
    }

    //查询指定日期范围内的员工数据
    @RequestMapping("/filterByDate")
    @ResponseBody
    public Map<String, Object> filterByDate(@RequestParam(required = true) String startDate, @RequestParam(required = true) String endDate,
                                            @RequestParam(defaultValue = "1") int page,
                                            @RequestParam(defaultValue = "8") int size)
    {
        //跳过多少条数据
        int offset = (page - 1) * size;

        //分页数据获取:调用 selectAllEmployeesPaginated 方法,传入 offset 和 size,返回满足条件的员工
        List<TestLpyEmployee> employees = dataBaseTableMapper.filterByDatePaginated(startDate, endDate, offset, size);
        //总数查询:调用 getTotalEmployeeCount 方法，获取员工总数，用于计算总页数
        int totalCount = dataBaseTableMapper.getTotalEmployeeCountFiltered(startDate, endDate);

        Map<String, Object> response = new LinkedHashMap<>();
        response.put("employees", employees);
        response.put("totalCount", totalCount);
        return response;
    }

    // 根据ID删除员工数据
    @DeleteMapping("/deleteEmployee/{employeeId}")
    @ResponseBody
    //@PathVariable:从url路径中提取动态参数
    public String deleteEmployee(@PathVariable(name = "employeeId") Integer employeeId) {
        dataBaseTableMapper.deleteEmployeeById(employeeId);
        return "删除成功";
    }

    // 更新员工数据
    @RequestMapping("/updateEmployee")
    //@ResponseBody:将HTTP请求体(Body)中的 JSON/XML 数据自动转换为 TestLpyEmployee 对象
    public String updateEmployee(@RequestBody TestLpyEmployee employee) {
        dataBaseTableMapper.updateEmployee(employee);
        return "更新成功";
    }

    //新增员工数据
    @RequestMapping("/addEmployee")
    public void addEmployee(@RequestBody TestLpyEmployee employee) {
        dataBaseTableMapper.insertEmployee(employee);
    }

    // 批量删除员工
    @RequestMapping("/deleteEmployees")
    @ResponseBody
    public String deleteEmployees(@RequestBody Map<String, List<Integer>> request) {
        List<Integer> employeeIds = request.get("employeeIds");
        if (employeeIds == null || employeeIds.isEmpty()) {
            return "没有选择要删除的员工";
        }
        try {
            dataBaseTableMapper.batchDelete(employeeIds);
            return "批量删除成功";
        } catch (Exception e) {
            return "批量删除失败：" + e.getMessage();
        }
    }

    //查询test_lpy_employee_submit表的所有数据
    @RequestMapping("/viewEmployeesSubmit")
    @ResponseBody
    public Map<String, Object> viewEmployeesSubmit(@RequestParam(defaultValue = "1") int page, @RequestParam(defaultValue = "8") int size) {
        int offset = (page - 1) * size;
        List<TestLpyEmployeeSubmit> submitData = dataBaseTableMapper.selectAllSubmitPaginated(offset, size);
        int totalCount = dataBaseTableMapper.getTotalSubmitCount();

        Map<String, Object> response = new LinkedHashMap<>();
        response.put("code", 200);
        response.put("msg", "获取报送表数据成功");
        response.put("data", submitData);
        response.put("totalCount", totalCount);
        return response;
    }

    // 根据日期查询test_lpy_employee_submit表(带分页)
    @RequestMapping("/viewEmployeesSubmitByDate")
    @ResponseBody
    public Map<String, Object> viewEmployeesSubmitByDate(@RequestParam(required = true) String date,
                                                         @RequestParam(defaultValue = "1") int page, @RequestParam(defaultValue = "8") int size) {
        List<TestLpyEmployeeSubmit> submitData = new ArrayList<>();
        int totalCount = 0;
        try {
            // 将字符串日期转换为java.sql.Date
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            java.util.Date utilDate = sdf.parse(date);
            java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());

            int offset = (page - 1) * size;
            submitData = dataBaseTableMapper.selectEmployeesSubmitByDatePaginated(sqlDate, offset, size);
            totalCount = dataBaseTableMapper.getTotalSubmitCountByDate(sqlDate);

            Map<String, Object> response = new LinkedHashMap<>();
            response.put("code", 200);
            response.put("msg", "获取按日期筛选的报送表数据成功");
            response.put("data", submitData);
            response.put("totalCount", totalCount);
            return response;
        } catch (Exception e) {
            Map<String, Object> errorResponse = new LinkedHashMap<>();
            errorResponse.put("code", 500);
            errorResponse.put("msg", "获取按日期筛选的报送表数据失败: " + e.getMessage());
            return errorResponse;
        }
    }

}

